<?php
/**
 *
 * @category    API
 * @package     Api_Exchange
 * @subpackage  Model
 * @author      trungpm
 */
class Api_Model_Exchange {
    const table_name = 'tblvgt_exchange';
    /*
     * delete exchange trungpm
     */
    function DeleteExchange($ImportDate, $partnerId, $partnerPrice, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $sql = 'DELETE FROM tblvgt_exchange WHERE
            exchange_imported_date = N\'' . $ImportDate . '\' AND partner_id = ' . $partnerId . ' AND exchange_price = ' . $partnerPrice;
        if (sizeof ( $Errors ) == 0) {
            return DB_Query ( $sql, $db );
        }
        return null;
    }

    /* Insert a new Exchange */
    function InsertExchange($ExchangeDetails, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        foreach ( $ExchangeDetails as $key => $value ) {
            $ExchangeDetails [$key] = DB_escape_string ( $value );
        }
        $FieldNames = '';
        $FieldValues = '';
        foreach ( $ExchangeDetails as $key => $value ) {
            $FieldNames .= $key . ', ';
            $FieldValues .= 'N\'' . str_replace ( "\'", "''", $value ) . '\', ';
        }
        $sql = 'INSERT INTO tblvgt_exchange (' . substr ( $FieldNames, 0, - 2 ) . ') ' . 'VALUES (' . substr ( $FieldValues, 0, - 2 ) . ') ';
        if (sizeof ( $Errors ) == 0) {
            $result = DB_Query ( $sql, $db );
            if (! $result) {
                $Errors [0] = DatabaseInsertFailed;
            }
        }
        return $Errors;
    }

    /* Get Yield Revenue */
    function GetYieldRevenue($PartnerId = 2, $PartnerPrice = '', $ObjectId, $Type = 1, $FromDate, $ToDate, $SingerAuthority = null, $ComposerAuthority = null, $ReleaseAuthority = null, $limit = null, $start = null, &$total, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $select = "@rownum:=@rownum+1 AS rownum, exchange_id,
                    DATE_FORMAT(exchange_imported_date, '%m/%Y') AS exchange_imported_date,
                    partner_id, partner_name,
                    song_number, song_name, singer_name, song_singer_sing, composer_name, exchange_price,
                    exchange_quantity,
                    song_singer_authority, song_composer_authority, song_release_authority,
                    (exchange_price*exchange_quantity*0.85) as money_total,
                    (exchange_price*exchange_quantity*0.85)*0.4 as money_composer,
                    (exchange_price*exchange_quantity*0.85)*0.3 as money_singer,
                    (exchange_price*exchange_quantity*0.85)*0.3 as money_release, singer_id, composer_id";

        $sql = "SELECT %s
                FROM tblvgt_exchange
                JOIN (SELECT @rownum:=0) r ON 1 = 1
                WHERE
                    exchange_imported_date >= '$FromDate'
                    AND exchange_imported_date <= '$ToDate'
                    AND locked = 0";

        // neu tim ca sy
        if (isset ( $ObjectId ) && ($ObjectId > 0)) {
            if ($Type == 1) {
                $sql .= " AND singer_id = $ObjectId";
            } elseif ($Type == 2) {
                $sql .= " AND composer_id = $ObjectId";
            }
        }
        if (isset ( $PartnerId ) && ($PartnerId > 0)) {
            $sql .= " AND partner_id = $PartnerId";
        }
        if (isset ( $SingerAuthority )) {
            $sql .= " AND song_singer_authority = $SingerAuthority";
        }
        if (isset ( $ComposerAuthority )) {
            $sql .= " AND song_composer_authority = $ComposerAuthority";
        }
        if (isset ( $ReleaseAuthority )) {
            $sql .= " AND song_release_authority = $ReleaseAuthority";
        }
        if (! empty ( $PartnerPrice )) {
            $sql .= " AND exchange_price = $PartnerPrice";
        }

        $sql1 .=  sprintf($sql, "count(exchange_id)");
        $stmt = DB_Query ( $sql1, $db );
        $obj = DB_fetch_object ( $stmt );
        $total = intval ( $obj->c );
        DB_free_result ( $result );

        $sql2 .= sprintf($sql, $select);
        $sql2 .= " ORDER BY exchange_imported_date, exchange_price, song_number";
        if (isset ( $start ) && isset ( $limit )) {
            $sql2 .= " LIMIT $start, $limit";
        }

        $result = DB_Query ( $sql2, $db );
        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
    }

    /* Get Sum Yield Revenue */
    function GetSumYieldRevenue($PartnerId = 2, $PartnerPrice = '', $ObjectId, $Type = 1, $FromDate, $ToDate, $SingerAuthority = null, $ComposerAuthority = null, $ReleaseAuthority = null, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $sql = "SELECT SUM(exchange_quantity) AS sum_exchange_quantity,
                    SUM(exchange_price*exchange_quantity) as sum_money_total,
                    SUM((exchange_price*exchange_quantity)*0.4) as sum_money_composer,
                    SUM((exchange_price*exchange_quantity)*0.3) as sum_money_singer,
                    SUM((exchange_price*exchange_quantity)*0.3) as sum_money_release
                FROM tblvgt_exchange
                WHERE locked = 0 AND exchange_imported_date >= '$FromDate'
                    AND exchange_imported_date <= '$ToDate'";
        // neu tim ca sy
        if (isset ( $ObjectId ) && ($ObjectId > 0)) {
            if ($Type == 1) {
                $sql .= " AND singer_id = $ObjectId";
            } elseif ($Type == 2) {
                $sql .= " AND composer_id = $ObjectId";
            }
        }
        if (isset ( $PartnerId ) && ($PartnerId > 0)) {
            $sql .= " AND partner_id = $PartnerId";
        }
        if (isset ( $SingerAuthority )) {
            $sql .= " AND song_singer_authority = $SingerAuthority";
        }
        if (isset ( $ComposerAuthority )) {
            $sql .= " AND song_composer_authority = $ComposerAuthority";
        }
        if (isset ( $ReleaseAuthority )) {
            $sql .= " AND song_release_authority = $ReleaseAuthority";
        }
        if (! empty ( $PartnerPrice )) {
            $sql .= " AND exchange_price = $PartnerPrice";
        }
        $result = DB_Query ( $sql, $db );
        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
    }

    /* Get Not Yield Revenue */
    function GetNotYieldRevenue($PartnerId = 2, $PartnerPrice, $ObjectId, $Type = 1, $FromDate, $ToDate, $SingerAuthority = null, $ComposerAuthority = null, $ReleaseAuthority = null, $limit = null, $start = null, &$total, $FromCreatedDate = null, $ToCreatedDate = null, $SongNumber = '', $SongName = '', $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $sqlExchange = "SELECT DISTINCT song_id
                        FROM tblvgt_exchange
                        WHERE locked = 0 AND exchange_imported_date >= '$FromDate'
                            AND exchange_imported_date <= '$ToDate'";
        if (isset ( $PartnerPrice ) && ! empty ( $PartnerPrice )) {
            $sqlExchange .= "AND exchange_price = $PartnerPrice";
        }
        $whereExchange = array ();
        if (isset ( $PartnerId ) && ($PartnerId > 0)) {
            $where [] = "s.partner_id = $PartnerId";
        }
        if (isset ( $ObjectId ) && ($ObjectId > 0)) {
            if ($Type == 1) {
                $where [] = "singer_id = $ObjectId";
            } elseif ($Type == 2) {
                $where [] = "composer_id = $ObjectId";
            }
        }
        if (isset ( $SingerAuthority )) {
            $where [] = "song_singer_authority = $SingerAuthority";
        }
        if (isset ( $ComposerAuthority )) {
            $where [] = "song_composer_authority = $ComposerAuthority";
        }
        if (isset ( $ReleaseAuthority )) {
            $where [] = "song_release_authority = $ReleaseAuthority";
        }
        $sqlExchange .= " AND " . implode ( " AND ", $where );

        // ////////////////////
        $sqlNotYield = "SELECT @rownum:=@rownum+1 AS rownum,
                            s.song_id,
                            s.type_id ,
                            s.partner_id,
                            s.singer_id,
                            oSinger.object_number as singer_number,
                            oComposer.object_number as composer_number,
                            s.composer_id,
                            s.song_number,
                            s.song_name,
                            s.song_unsigned_name,
                            s.song_price,
                            s.song_singer_sing,
                            s.song_singer_authority,
                            s.song_composer_authority,
                            s.song_release_authority,
                            oSinger.object_name AS singer_name,
                            oComposer.object_name As composer_name,
                            oPartner.partner_name,
                            convert(varchar, s.ended_date, 101) as ended_date,
                            convert(varchar, s.created_date, 101) as created_date,
                            convert(varchar, s.updated_date, 101) as updated_date
                        FROM tblvgt_song as s
                        LEFT JOIN tblvgt_object AS oSinger ON s.singer_id = oSinger.object_id
                        LEFT JOIN tblvgt_object AS oComposer ON s.composer_id = oComposer.object_id
                        LEFT JOIN tblvgt_partner AS oPartner ON s.partner_id = oPartner.partner_id
                        JOIN (SELECT @rownum:=0) r ON 1 = 1
                        WHERE s.locked = 0
                        AND s.song_id NOT IN ($sqlExchange)" .
                        " AND " . implode ( " AND ", $where );

        if (isset ( $FromCreatedDate ) && ! empty ( $FromCreatedDate )) {
            $sqlNotYield .= " AND s.created_date >= '$FromCreatedDate'";
        }
        if (isset ( $ToCreatedDate ) && ! empty ( $ToCreatedDate )) {
            $sqlNotYield .= " AND s.created_date <= '$ToCreatedDate'";
        }
        if (isset ( $SongNumber ) && ! empty ( $SongNumber )) {
            $sqlNotYield .= " AND s.song_number LIKE '%$SongNumber%'";
        }
        if (isset ( $SongName ) && ! empty ( $SongName )) {
            $sqlNotYield .= " AND s.song_name LIKE '%$SongName%'";
        }

        $sql1 .= "SELECT count(*) as c FROM(" . $sqlNotYield . ") AS A";
        $stmt = DB_Query ( $sql1, $db );
        $obj = DB_fetch_object ( $stmt );
        $total = intval ( $obj->c );
        DB_free_result ( $result );


        $sql2 .= "SELECT * FROM(" . $sqlNotYield . ") AS A ";
        if (isset ( $start ) && isset ( $limit )) {
            $sql2 .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit))";
        }
        $sql2 .= " ORDER BY created_date ASC";

        $result = DB_Query ( $sql2, $db );
        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
    }

    /*
     * get row.
     */
    function getRow($id) {
        // Check user permission
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        $sql = "SELECT * FROM " . self::table_name . " WHERE exchange_id = $id";
        $row = DB_query ( $sql, $db );
        return DB_fetch_array ( $row );
    }

    /*
     * Verify field existed.
     */
    function verifyFieldExist($field, $value) {
        // Check user permission
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        ;
        $Searchsql = "SELECT count(*) as c FROM " . self::table_name . " WHERE $field = N'$value'";
        $SearchResult = DB_query ( $Searchsql, $db );
        $answer = DB_fetch_array ( $SearchResult );
        if ($answer ['c'] != 0) {
            return 1;
        }
        ;
        return 0;
    }

    /*
     * update huudatxm
     */
    function update($data) {
        $Errors = array ();
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $exchange_id = $data ['exchange_id'];
        $checkId = $this->verifyFieldExist ( 'exchange_id', $exchange_id );
        if ($checkId == 0)
            return 0;

        $val_str = $data ['field'] . ' = N\'' . $data ['value'] . '\',';
        $val_str .= ' updated_date = N\'' . date ( 'Y-m-d' ) . '\' ';
        $val_str .= ', updated_by = N\'' . $_SESSION ['UserID'] . '\' ';

        $sql = "UPDATE " . self::table_name . " SET $val_str WHERE exchange_id = $exchange_id";

        if (sizeof ( $Errors ) == 0) {
            $result = DB_Query ( $sql, $db );
            if ($result)
                return true;
        }
        return 0;
    }

    /*
     * delete huudatxm
     */
    function delete($exchange_id, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $checkId = $this->verifyFieldExist ( 'exchange_id', $exchange_id );
        if ($checkId == 1) {
            $val_str = 'locked = 1';
            $val_str .= ', updated_date = N\'' . date ( 'Y-m-d' ) . '\' ';
            $val_str .= ', updated_by = N\'' . $_SESSION ['UserID'] . '\' ';
            $sql = "UPDATE " . self::table_name . " SET $val_str WHERE exchange_id = $exchange_id";

            if (sizeof ( $Errors ) == 0) {
                return DB_Query ( $sql, $db );
            }
        }
        return null;
    }

    /* Get Summary Yield Revenue */
    function GetSummaryYieldRevenue($PartnerId = 2, $FromDate, $ToDate, &$list_detail, &$list_song, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        // GET LIST DETAIL
        $sql = "SELECT
                    exchange_id, dbo.fnFormatDate (exchange_imported_date,'MM/YYYY') as exchange_imported_date,
                    song_number, song_name, exchange_quantity
                FROM tblvgt_exchange
                WHERE locked = 0 AND exchange_imported_date >= '$FromDate'
                    AND exchange_imported_date <= '$ToDate'" . "
                    AND partner_id = $PartnerId
                ORDER BY exchange_imported_date";
        $list_detail = DB_Query ( $sql, $db );

        // GET
        $sql = "SELECT
                    tblvgt_exchange.song_number, MAX(tblvgt_exchange.song_name) as song_name,
                     MAX (tblvgt_exchange.singer_name) as singer_name,
                    MAX (tblvgt_exchange.composer_name) as composer_name,
                     SUM(tblvgt_exchange.exchange_quantity) as exchange_quantity
                FROM tblvgt_exchange
                WHERE tblvgt_exchange.locked = 0
                AND tblvgt_exchange.exchange_imported_date >= '$FromDate'
                AND tblvgt_exchange.exchange_imported_date <= '$ToDate'
                AND tblvgt_exchange.partner_id = " . $PartnerId . "
                group by tblvgt_exchange.song_number, tblvgt_exchange.song_name
                ORDER BY tblvgt_exchange.song_number";
        $list_song = DB_Query ( $sql, $db );

        if (DB_has_rows ( $list_detail ) == 0 && DB_has_rows ( $list_song ) == 0) {
            return false;
        } else {
            return true;
        }
    }

    /*
     * author: trungpm
     */
    function GetSongsNerverExchanged($partnerId, $user, $password) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $sql = 'SELECT song.song_id,
            song.song_number,
            song.song_name,
            song.song_unsigned_name
        FROM tblvgt_song as song
        WHERE song.partner_id = ' . $partnerId . ' AND song.song_number NOT IN (
            SELECT DISTINCT song_number FROM tblvgt_exchange WHERE partner_id = ' . $partnerId . ')
        ORDER BY song.song_number';
        return DB_Query ( $sql, $db );
    }
}